package com.zengyan.gd.utils;

import com.zengyan.gd.core.ServiceException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {


    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            return false;
        }
        return true;
    }


    public static List<String> readExcelValue(MultipartFile mfile) throws Exception {
        List<String> list = new ArrayList<>();
        InputStream inputStream = null;
        try {
            inputStream = mfile.getInputStream();
            Workbook wb;
//            if (ExcelUtils.isExcel2007(mfile.getName())) {
//                wb = new HSSFWorkbook(inputStream);
//            } else {
                wb = new XSSFWorkbook(inputStream);
//            }
            Sheet sheet = wb.getSheetAt(0);
            System.out.println("count = " + wb.getNumberOfSheets());
            for (int i = 0; i < sheet.getLastRowNum() + 1; i++) {
                Row row1 = sheet.getRow(i);
                Cell cell = row1.getCell(0);
                DecimalFormat df = new DecimalFormat("0");
                String stuNum = df.format(cell.getNumericCellValue());
                if (!stuNum.equals("0")) {
                    list.add(stuNum);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new ServiceException("excel解析出错");
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    inputStream = null;
                    e.printStackTrace();
                }
            }
        }
        if (list.isEmpty()) throw new ServiceException("数据解析为空，失败");
        return list;
    }


}
